# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import openpyxl
import pymysql


# 保存到数据库
class DBPipeline:
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', database='spider',
                                    charset='utf8mb4')
        self.cursor = self.conn.cursor()
        self.data = []

    def close_spider(self, spider):
        if len(self.data) > 0:
            self._batch_to_DB()
            self.conn.close()

    def process_item(self, item, spider):
        details_url = item.get('details_url') or ''
        title = item.get('title') or ''
        source = item.get('source') or ''
        release_time = item.get('release_time') or ''
        context = item.get('context') or ''
        self.data.append((details_url, title, source, release_time, context))
        if len(self.data) == 100:
            self._batch_to_DB()
            self.data.clear()
        return item

    #批量存入数据库
    def _batch_to_DB(self):
        self.cursor.executemany(
            'INSERT into `spider_data` (`details_url`,`title`,`source`,`release_time`,`context`) values(%s,%s,%s,%s,%s)',
            self.data
        )
        self.conn.commit()


# 输出到excel
class ExcelPipeline:
    def __init__(self):
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = '标题测试'
        self.ws.append(('来源地址', '标题', '信息来源', '发布时间', '内容'))

    def close_spider(self, spider):
        self.wb.save('爬取数据.xlsx')

    def process_item(self, item, spider):
        details_url = item.get('details_url') or ''
        title = item.get('title') or ''
        source = item.get('source') or ''
        release_time = item.get('release_time') or ''
        context = item.get('context') or ''
        self.ws.append((details_url, title, source, release_time, context))
        return item
